import re
from flask import Flask, jsonify, request,json
from flask_cors import cross_origin
import queryTool
import requests
import insertTool
import os
import oss2
import datetime

# key和secret，OSS控制台可以拿到
access_key_id = os.getenv('OSS_TEST_ACCESS_KEY_ID', 'LTAI5t9ntrpeo96ZvHRqCpme')
access_key_secret = os.getenv('OSS_TEST_ACCESS_KEY_SECRET', 'Gdo9T2fGZzOhgksu77gQmAQoOxc7IB')
bucket_name = os.getenv('OSS_TEST_BUCKET', 'trace666')
endpoint = os.getenv('OSS_TEST_ENDPOINT', 'http://oss-cn-beijing.aliyuncs.com')


# 创建Bucket对象
bucket = oss2.Bucket(oss2.Auth(access_key_id, access_key_secret), endpoint, bucket_name)


app = Flask(__name__)
app.config['JSON_AS_ASCII'] = False

# 天气的接口
@app.route('/getWeather', methods=["GET"])
@cross_origin()
def getWeather():
    city = keyword = request.args.get("city")
    datas = {"key":"d1586ee8928580d84da6696b4cd080c5","city":city}
    weatherInfo = requests.post('http://apis.juhe.cn/simpleWeather/query',data=datas)
    print(weatherInfo)
    assign = weatherInfo.json()
    return json.dumps(assign)

# 保存的方法
@app.route('/save', methods=["POST"])
@cross_origin()
def save():
    data = json.loads(request.get_data(as_text=True))
    createTime = datetime.datetime.now()
    openId = data['openId']
    travelList = data['travelList']
    cover = data['cover']
    cover = cover[1:]
    urlArray = cover.split('?')
    travelSql = "insert into travel (createTime,openId,cover) values('%s','%s','%s')" % (createTime, openId,urlArray[0])
    travelId = insertTool.my_db(travelSql)
    for travel in travelList:
        title = travel['title']
        content = travel['content']
        time = travel['time']
        address = travel['address']
        latitude = travel['latitude']
        longitude = travel['longitude']
        file = travel['file']
        sql = "insert into addressInfo (travelId,title,content,time,address,latitude,longitude) values('%s','%s','%s','%s','%s','%s','%s')" % (travelId,title,content,time,address,latitude,longitude)
        id = insertTool.my_db(sql)
        for url in file:
            subUrl = url[1:]
            subArray = subUrl.split('?')
            fileSql = "insert into addressfile (addressId,fileUrl) values('%s','%s')" % (id,subArray[0])
            insertTool.my_db(fileSql)
    return jsonify("sucess")

# 获取所有定位点信息的方法
@app.route('/initData', methods=["GET"])
@cross_origin()
def initData():
    travelSql = "select id,createTime,cover,openId from travel"
    travelList = queryTool.my_db(travelSql)
    for travel in travelList:
        sql = "select id,time,address,title,content,latitude,longitude from addressInfo where travelId = '%s'" %(travel['id'])
        data = queryTool.my_db(sql)
        collectsql = "select * from collect where travelId = '%s'" %(travel['id'])
        collectdata = queryTool.my_db(collectsql)
        travel['collectCount'] = collectdata
        praisesql = "select * from praise where travelId = '%s'" % (travel['id'])
        praisedata = queryTool.my_db(praisesql)
        travel['praiseCount'] = praisedata
        for addressInfo in data:
            subsql = "select id,addressId,fileUrl from addressfile where addressId = '%s'" %(addressInfo['id'])
            subdata = queryTool.my_db(subsql)
            addressInfo['file'] = subdata
        travel['addressInfo'] = data
    return jsonify(travelList)

# 根据地点模糊查询
@app.route('/findByAddressLike', methods=["GET"])
@cross_origin()
def findByAddressLike():
    keyword = request.args.get("keyword")
    keyword = "%"+keyword+"%"
    travelSql = "select DISTINCT a.* from travel a left join addressInfo b on a.id=b.travelId where b.address like '%s'"  % (keyword)
    travelList = queryTool.my_db(travelSql)
    for travel in travelList:
        sql = "select id,time,address,title,content,latitude,longitude from addressInfo where travelId = '%s'" % (travel['id'])
        data = queryTool.my_db(sql)
        for addressInfo in data:
            subsql = "select id,addressId,fileUrl from addressfile where addressId = '%s'" % (addressInfo['id'])
            subdata = queryTool.my_db(subsql)
            addressInfo['file'] = subdata
        travel['addressInfo'] = data
    return jsonify(travelList)

# 根据用户得openId获取用户信息
@app.route('/findByOpenId', methods=["GET"])
@cross_origin()
def findByOpenId():
    openId = request.args.get("openId")
    travelSql = "select id,createTime,cover,openId from travel where openId = '%s'" %(openId)
    travelList = queryTool.my_db(travelSql)
    for travel in travelList:
        sql = "select id,time,address,title,content,latitude,longitude from addressInfo where travelId = '%s'" %(travel['id'])
        data = queryTool.my_db(sql)
        for addressInfo in data:
            subsql = "select id,addressId,fileUrl from addressfile where addressId = '%s'" %(addressInfo['id'])
            subdata = queryTool.my_db(subsql)
            addressInfo['file'] = subdata
        travel['addressInfo'] = data
    return jsonify(travelList)

# 根据id查询
@app.route('/findById', methods=["GET"])
@cross_origin()
def findById():
    # 获取前端传来的travel的id
    travelId = request.args.get("id")
    # 根据id查询travel信息
    travelSql = "select id,createTime,openId,cover from travel where id = '%s'" % (travelId)
    travelList = queryTool.my_db(travelSql)
    # 循环根据travelList查询每个travel下的addressInfo信息
    for travel in travelList:
        sql = "select id,time,address,title,content,latitude,longitude from addressInfo where travelId = '%s'" %(travel['id'])
        addressInfoList = queryTool.my_db(sql)
        for addressInfo in addressInfoList:
            subsql = "select id,addressId,fileUrl from addressfile where addressId = '%s'" %(addressInfo['id'])
            subdata = queryTool.my_db(subsql)
            addressInfo['file'] = subdata
        travel['addressInfo'] = addressInfoList;
    return jsonify(travelList)

# 收藏
@app.route('/collectByTravelId', methods=["GET"])
@cross_origin()
def collectByTravelId():
    travelId = request.args.get("travelId")
    openId = request.args.get("openId")
    createTime = datetime.datetime.now()
    collectSql = "insert into collect (createTime,travelId,openId) values('%s','%s','%s')" % (createTime, travelId, openId)
    insertTool.my_db(collectSql)
    return jsonify('收藏成功')

# 收藏数量
@app.route('/countCollectByTravelId', methods=["GET"])
@cross_origin()
def countCollectByTravelId():
    travelId = request.args.get("travelId")
    collectSql = "select * from collect where travelId = '%s'" % (travelId)
    data = queryTool.my_db(collectSql)
    return jsonify(data)

# 查询是否已经收藏
@app.route('/findCollectByTravelIdAndOpendId', methods=["GET"])
@cross_origin()
def findCollectByTravelIdAndOpendId():
    travelId = request.args.get("travelId")
    openId = request.args.get("openId")
    collectSql = "select * from collect where travelId = '%s' and openId = '%s'" % (travelId, openId)
    data = queryTool.my_db(collectSql)
    if len(data) > 0:
        return jsonify({
            "colleced": True
        })
    else:
        return jsonify({
            "colleced": False
        })
    # return jsonify(data)

# 点赞
@app.route('/praiseByTravelId', methods=["GET"])
@cross_origin()
def praiseByTravelId():
    travelId = request.args.get("travelId")
    openId = request.args.get("openId")
    createTime = datetime.datetime.now()
    praiseSql = "insert into praise (createTime,travelId,openId) values('%s','%s','%s')" % (createTime, travelId, openId)
    insertTool.my_db(praiseSql)
    return jsonify('点赞成功')

# 查询是否已经点赞
@app.route('/findPraiseByTravelIdAndOpendId', methods=["GET"])
@cross_origin()
def findPraiseByTravelIdAndOpendId():
    travelId = request.args.get("travelId")
    openId = request.args.get("openId")
    createTime = datetime.datetime.now()
    collectSql = "select * from praise where travelId = '%s' and openId = '%s'" % (travelId, openId)
    data = queryTool.my_db(collectSql)
    if len(data) > 0:
        return jsonify({
            "praised": True
        })
    else:
        return jsonify({
            "praised": False
        })
    # return jsonify(data) 

# 收藏数量
@app.route('/countPraiseByTravelId', methods=["GET"])
@cross_origin()
def countPraiseByTravelId():
    travelId = request.args.get("travelId")
    collectSql = "select * from praise where travelId = '%s'" % (travelId)
    data = queryTool.my_db(collectSql)
    return jsonify(data)

# 评论
@app.route('/commentByTravelId', methods=["POST"])
@cross_origin()
def commentByTravelId():
    data = json.loads(request.get_data(as_text=True))
    createTime = datetime.datetime.now()
    openId = data['openId']
    travelId = data['travelId']
    comment = data['comment']
    commentSql = "insert into comment (createTime,travelId,openId,comment) values('%s','%s','%s','%s')" % (createTime, travelId, openId, comment)
    insertTool.my_db(commentSql)
    return jsonify('评论成功')

# 评论展示
@app.route('/findCommentByTravelId', methods=["GET"])
@cross_origin()
def findCommentByTravelId():
    travelId = request.args.get("travelId")
    commentSql = "select * from comment where travelId = '%s'" % (travelId)
    data = queryTool.my_db(commentSql)
    return jsonify(data)

# 根据收藏查询
@app.route('/findByCollect', methods=["GET"])
@cross_origin()
def findByCollect():
    openId = request.args.get("openId")
    travelSql = "select a.id,a.createTime,a.cover,a.openId from travel a inner join collect b on a.id = b.travelId where b.openId = '%s'" % (openId)
    travelList = queryTool.my_db(travelSql)
    for travel in travelList:
        sql = "select id,time,address,title,content,latitude,longitude from addressInfo where travelId = '%s'" % (travel['id'])
        data = queryTool.my_db(sql)
        for addressInfo in data:
            subsql = "select id,addressId,fileUrl from addressfile where addressId = '%s'" % (addressInfo['id'])
            subdata = queryTool.my_db(subsql)
            addressInfo['file'] = subdata
        travel['addressInfo'] = data
    return jsonify(travelList)

@app.route('/upload', methods=['GET', 'POST'])
@cross_origin()
def index():
    # 上传文件到服务器
    file = request.files.get('file')
    if file is None:
        return '没有检索到文件'
    else:
        # 上传文件到阿里云OSS
        res = bucket.put_object(file.filename, file)
        if res.status == 200:
            # 上传成功，获取文件带签名的地址，返回给前端
            url = bucket.sign_url('GET', file.filename, 60)
            return json.dumps(url)

def query():
    sql = "select * from travel "
    data=[]
    data = queryTool.my_db(sql)

    return jsonify(data)



if __name__ == '__main__':
    app.run(host='0.0.0.0',port=5000)

